package com.ruoyi.project.newhope.gather;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ruoyi.project.newhope.data.domain.OPCTagRead;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author xizongren
 * @date 2021/3/4 3:52 下午
 */
@Slf4j
public class C3P0Utils {
    private final ComboPooledDataSource dataSource = new ComboPooledDataSource();

    /**
     * jdbc:sqlserver://10.180.149.10:1433;SelectMethod=cursor;DatabaseName=ProcessPlus_OPC
     */
    public C3P0Utils(String jdbcUrl, String driverClass, String user, String pwd){
        log.info("C3P0 连接信息 >>> user: {}; pwd: {}; jdbcUrl: {}; driverClass: {};", user, pwd, jdbcUrl, driverClass);
        try {
            dataSource.setDriverClass(driverClass);
            dataSource.setUser(user);
            dataSource.setPassword(pwd);
            dataSource.setJdbcUrl(jdbcUrl);
            dataSource.setLoginTimeout(60);
            dataSource.setCheckoutTimeout(60 * 1000);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取链接
     */
    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (Exception e) {
            log.error("获取连接异常", e);
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 查询通用方法
     */
    public static <T> List<T> query(String sql, Connection connection, ResultHandler handler) throws SQLException {
        List<T> list = new ArrayList<>();
        try {
            PreparedStatement st = connection.prepareStatement(sql);
            ResultSet rs = st.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()) { list.add(handler.getRow(rs, metaData)); }
        } catch (Exception e) {
            log.error("数据查询异常", e);
            throw e;
        } finally {
            release(connection);
        }
        return list;
    }

    /**
     * 数据更新通用方法
     */
    public static int update(String sql, Connection connection, ParameterHandler handler){
        int updateNum = -1;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            handler.setParam(ps);
            updateNum = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("数据更新异常", e);
        } finally {
            release(connection);
        }
        return updateNum;
    }

    interface ParameterHandler { void setParam(PreparedStatement ps);}

    interface ResultHandler { <T> T getRow(ResultSet rs, ResultSetMetaData metaData) throws SQLException;}

    /**
     * select * from OPCTagRead where Tag_ID = "Out/Constituents/水分" or Tag_ID = "Out/Constituents/蛋白"
     */
    public static List<OPCTagRead> queryTag(Connection connection, String sql) throws SQLException {
        return query(sql, connection, new C3P0Utils.ResultHandler() {
            @Override
            public <T> T getRow(ResultSet rs, ResultSetMetaData metaData) {
                OPCTagRead read = new OPCTagRead();
                try {
                    for (int i = 0; i < metaData.getColumnCount(); i++) {
                        Object columnValue = rs.getObject(i + 1);
                        String columnLabel = metaData.getColumnLabel(i + 1);
                        if ("Tag_ID".equals(columnLabel)) { read.setTagId(((String) columnValue).trim());}
                        if ("OPCTime".equals(columnLabel)) { read.setOpcTime((Date) columnValue);}
                        if ("ValNum".equals(columnLabel)) { read.setValNum(((Float)columnValue));}
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    log.error("OPCTagRead 查询异常", e);
                }
                return (T) read;
            }
        });
    }

    /**
     * 关闭多个连接
     */
    public static void release(Connection conn, PreparedStatement pst, ResultSet rs) {
        if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
        if (pst != null) { try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } }
        if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } }
    }

    /**
     * 关闭connection连接
     */
    public static void release(Connection conn) {
        if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } }
    }
}
